mysql数据库递归查询树形结构（适用场景：菜单多级分类，多级关联评论查询），用strea流把list转成树的方法详解 |
您所在的位置：网站首页 › AdminLTE2 怎么展示树 › mysql数据库递归查询树形结构（适用场景：菜单多级分类，多级关联评论查询），用strea流把list转成树的方法详解 |
mysql数据库递归查询树形结构（适用场景：菜单多级分类，多级关联评论查询），用strea流把list转成树的方法详解
|
1.创建表以及数据：
DROP TABLE IF EXISTS `dealer_permissions`;
CREATE TABLE `dealer_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '模块名称',
`parent_Id` int(11) NULL DEFAULT NULL COMMENT '父id',
`operation_value` int(1) NULL DEFAULT NULL COMMENT '操作值',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `dealer_permissions` VALUES (1, '系统', NULL, NULL);
INSERT INTO `dealer_permissions` VALUES (2, '代理商', 1, NULL);
INSERT INTO `dealer_permissions` VALUES (3, '查询', 2, 100);
INSERT INTO `dealer_permissions` VALUES (4, '新增代理商', 2, 101);
INSERT INTO `dealer_permissions` VALUES (5, '密码还原', 2, 102);
INSERT INTO `dealer_permissions` VALUES (7, '修改', 2, 103);
INSERT INTO `dealer_permissions` VALUES (8, '代理商用户详情', 2, 104);
INSERT INTO `dealer_permissions` VALUES (9, '职员', 1, NULL);
INSERT INTO `dealer_permissions` VALUES (10, '查询', 9, 105);
INSERT INTO `dealer_permissions` VALUES (11, '新增', 9, 106);
2.数据以及层次关系：
层次关系：
现在的需求是把这个层级关系，在前端显示出来，后端的处理方法有两种： 1.直接把全部的数据从数据库中拿到，然后在java代码里面使用树形结构来进行解析，但是这种做法只能在数据量比较小的时候使用，然后数据量一大会造成内存溢出 2.在mysql中创建一个函数，传递一个根节点的id，把对应的所有叶子节点的数据查出来（如果对用上述的图，就是把“系统”的id传递进去，把代理商，职员，以及各自对应的下级找出来） 今天就介绍第二种，在此之前先来的学一下两个函数： find_in_set 函数语法：find_in_set(str,strlist) select * from dealer_permissions where FIND_IN_SET(id,'1,2,3');结果是返回id在表中的记录，即 id = '1' ，id = '2' ，id = '3' 三条记录。
可以使用函数：以向下递归查询所有子节点为例。只要找到一个包含当前节点和所有子节点的以逗号拼接的字符串 strlist，传进 find_in_set 函数。就可以查询出所有需要的递归数据了。 group_concat 函数（CONCAT_WS函数也是差不多的功能）：使用该函数，可以把表中的所有 id 以逗号拼接（该函数不只是这个作用，更多用处自行百度） select GROUP_CONCAT(id) from dealer_permissions;
自定义一个函数，通过传入根节点id，找到它的所有子节点： delimiter $$ drop function if exists get_child_list$$ create function get_child_list(in_id varchar(10)) returns varchar(1000) begin declare ids varchar(1000) default ''; declare tempids varchar(1000); set tempids = in_id; while tempids is not null do set ids = CONCAT_WS(',',ids,tempids); select GROUP_CONCAT(id) into tempids from dealer_permissions where FIND_IN_SET(parent_Id,tempids)>0; end while; return ids; end $$ delimiter ;（1） delimiter 用于定义结束符。我们知道MySQL默认的结束符为分号，表明指令结束并执行。但是在函数体中，有时我们希望遇到分号不结束，因此需要暂时把结束符改为一个随意的其他值。我这里设置为$$ ，用于定义结束符。 （2）drop function if exists get_child_list 若函数getchildlist已经存在了，则先删除它。注意这里需要用当前自定义的结束符 来结束并执行语句。 因为，这里需要和下边的函数体单独区分开来。 （3）create function get_child_list 创建函数。并且参数传入一个根节点的子节点id，需要注意一定要注明参数的类型和长度，如这里是 varchar(10)。然后returns varchar(1000) 用来定义返回值参数类型。 （4）begin 和 end 中间包围的就是函数体。用来写具体的逻辑。 （5）declare 用来声明变量，并且可以用 default 设置默认值。 这里定义的 ids 即作为整个函数的返回值，是用来拼接成最终我们需要的以逗号分隔的递归串的。 而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。 （6） set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。 （7） while do ... end while; 循环语句，循环逻辑包含在内。注意，end while 末尾需要加上分号。 循环体内，先用 CONCAT_WS 函数把最终结果 ids 和 临时生成的 tempids 用逗号拼接起来。 然后以 FIND_IN_SET(parent_Id,tempids)>0 为条件，遍历在 tempids 中的所有 parent_Id，寻找以此为父节点的所有子节点 id ，并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来，并覆盖更新 tempids 。 等下次循环进来时，就会再次拼接 ids ，并再次查找所有子节点的所有子节点。循环往复，一层一层的向下递归遍历子节点。直到判断 tempids 为空，说明所有子节点都已经遍历完了，就结束整个循环。 这里，用 '1' 来举例，即是：（参看图1的表数据关系） 第一次循环： tempids=1 ids=1 tempids=2,9(1的所有子节点) 第二次循环： tempids=2,9 ids=1,2,9 tempids=3,4,5,6,7,8,10,11 (2和9 的所有子节点) 第三次循环： tempids=3,4,5,6,7,8,10,11 ids=1,2,9,3,4,5,6,7,8,10,11 tempids=3,4,5,6,7,8,10,11的所有子节点 因找不到任何一个子节点，所以 tempids=null，就结束循环。 （8）return ids; 用于把 ids 作为函数返回值返回。 （9）函数体结束以后，记得用结束符 $$ 来结束整个逻辑，并执行。 （10）最后别忘了，把结束符重新设置为默认的结束符分号 。
执行代码： select * from dealer_permissions where FIND_IN_SET(id,get_child_list('1'));
可以看到，已经把需要找的数据找到了，然后在java中用代码做相对应的处理即可 手动实现递归查询（向上递归）相对于向下递归来说，向上递归比较简单。 因为向下递归时，每一层递归一个父节点都对应多个子节点。 而向上递归时，每一层递归一个子节点只对应一个父节点，关系比较单一。 同样的，我们可以定义一个函数 get_parent_list 来获取根节点的所有父节点。 delimiter $$ drop function if exists get_parent_list$$ create function get_parent_list(in_id varchar(10)) returns varchar(1000) begin declare ids varchar(1000); declare tempid varchar(10); set tempid = in_id; while tempid is not null do set ids = CONCAT_WS(',',ids,tempid); select parent_Id into tempid from dealer_permissions where id=tempid; end while; return ids; end $$ delimiter ; select * from dealer_permissions where FIND_IN_SET(id,get_parent_list('11'));执行的结果：
我们用到了 group_concat 函数来拼接字符串。但是，需要注意它是有长度限制的，默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。 注意，单位是字节，不是字符。在 MySQL 中，单个字母占1个字节，而我们平时用的 utf-8下，一个汉字占3个字节。 这个对于递归查询还是非常致命的。因为一般递归的话，关系层级都比较深，很有可能超过最大长度。（尽管一般拼接的都是数字字符串，即单字节） 所以，我们有两种方法解决这个问题： 1. 修改 MySQL 配置文件 my.cnf ，增加 group_concat_max_len = 102400 #你要的最大长度. 2.执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400;他们的区别在于，global是全局的，任意打开一个新的会话都会生效，但是注意，已经打开的当前会话并不会生效。而 session 是只会在当前会话生效，其他会话不生效。 共同点是，它们都会在 MySQL 重启之后失效，以配置文件中的配置为准。所以，建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节，也能拼上一万个id了。 除此之外，使用 group_concat 函数还有一个限制，就是不能同时使用 limit 。如， 本来只想查5条数据来拼接，现在不生效了。
不过，如果需要的话，可以通过子查询来实现，
show function status; 删除自定义函数：drop function 函数名; 在mybatis-plus中获取结果：先把自定义的方法在mysql中执行先，然后我们在这个表对应的mapper文件中：
使用@select注解，把要执行的语句添加进去 /** * 获取id下的所有子节点 */ @Select("select * from dealer_permissions where FIND_IN_SET(id,get_child_list(#{id}));") List getAllByIdLower(@RequestParam(value = "id") Integer id); 通过代码把返回的结果转成树： // 获取到了1节点下的所有数据（包括本节点） List allByIdLower = permissionsMapper.getAllByIdLower(1);
allByIdLower集合中就包括了以上的数据，现在通过stream流的方式，把结果转成树 先定义一个方法： /** * 链表转树 * @return */ public static List listToTree(DealerPermissionsModel root,List list) { return list.stream() .filter(a -> Objects.equals(a.getParentId(), root.getId())) .peek(m -> m.setModelList(listToTree(m, list))) .collect(Collectors.toList()); }调用方法： // 获取到了1节点下的所有数据（包括本节点） List allByIdLower = permissionsMapper.getAllByIdLower(1); // 排序成树的形式返回 List collect = allByIdLower .stream() .filter(a -> a.getParentId() == null) .peek(m -> m.setModelList(listToTree(m, allByIdLower))) .collect(Collectors.toList());输出collect：
首先通过： List collect = allByIdLower .stream() .filter(a -> a.getParentId() == null) .peek(m -> m.setModelList(listToTree(m, allByIdLower))) .collect(Collectors.toList());上面这段代码会进入了listToTree方法，此时的m是父节点为null的元素，因为经过了 filter(a -> a.getParentId() == null) 筛选，所以第一次进入listToTree方法的肯定是最顶层的父元素（对应到数据中，就是名称为 "系统" 的 数据） 然后就进入了listToTree，这个时候的listToTree方法的roo节点是名称为 "系统" 的数据，list是全部的数据，然后循环list集合通过filter来寻找list中有哪个元素的父节点等于root节点的id的数据（在这一步实际上就是循环找"系统"元素的一个子节点）循环到list的第二个元素的时候（第二个元素是名称为"代理商"的元素），因为“代理商”的父id是1，系统的id也是1，所以满足元素的父节点的id等于root的id条件，所以就会执行peek方法，然后把当前元素（名称为"代理商"的元素）以及list集合传递到listToTree方法 再一次进入到listToTree后，此时的root节点就是（名称为"代理商"的元素），然后又进行filter方法来循环查询父节点是代理商的元素，经过循环会找到（id为3，名称为"查询"）的元素，然后就会通过peek方法来查询该元素有那个子节点，如果找不到就返回，就这样子递归寻找下去... 以上就是全部内容，如果觉得对您有帮助，可以点个赞👍喔 |
【本文地址】